package com.hnkywl.sts.dao.flow;

import com.hnkywl.sts.entity.flow.FlowConfigure;
import com.hnkywl.sts.entity.flow.FlowTask;
import com.hnkywl.sts.entity.sys.Yg;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.transform.Transformers;
import org.jbpm.api.task.Task;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-4-7
 * Time: 下午12:36
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class FlowDao extends SimpleHibernateTemplate<FlowTask, Long> {
    public List<Map> findOwnerTask(Long ygId) {
        String hql = "select  task  from  org.jbpm.pvm.internal.task.TaskImpl  task left  join  task.participations  ppi  " +
                "where  ppi.task = task.dbid  and  ppi.type='candidate'  and (task.assignee is null) " +
                "and  (  ppi.userId = (select yg.zgjg.id from Yg yg left join yg.ssgwList gwList where gwList.id=-1 and yg.id=?)   " +
                "or   ppi.groupId  in  ( select  distinct  g.id   from org.jbpm.pvm.internal.identity.impl.MembershipImpl m " +
                " join  m.user u   join m.group g where  u.id = (select yg.zgjg.id from Yg yg left join yg.ssgwList gwList where gwList.id=-1 and yg.id=?) ) ) " +
                "order by task.createTime desc  ";
        List<Task> taskList = this.find(hql, ygId, ygId);
        List<Map> objList = new ArrayList<Map>();
        for (Task task : taskList) {
            Map<Object, Object> map = new HashMap<Object, Object>();
            String executionId;
            if (task.getExecutionId().indexOf(".") != task.getExecutionId().lastIndexOf("."))
                executionId = task.getExecutionId().substring(0, task.getExecutionId().lastIndexOf("."));
            else
                executionId = task.getExecutionId();
            List<Object[]> list = this.findTopHistory(executionId);
            map.put("id", task.getId());
            map.put("name", task.getName());
            map.put("activityName", task.getActivityName());
            map.put("executionId", task.getExecutionId());
            if (list != null && list.size() > 0) {
                map.put(list.get(0)[0], list.get(0)[1]);
                map.put(list.get(1)[0], list.get(1)[1]);
                map.put(list.get(2)[0], list.get(2)[1]);
            }
            objList.add(map);
        }
        return objList;
    }

    public List findTopHistory(String executeId) {
        String sql = "select * from (select VARNAME_,VALUE_ from JBPM4_HIST_VAR t where PROCINSTID_=? and VARNAME_!='owner' order by DBID_ desc) where rownum<=3";
        return this.getSession().createSQLQuery(sql).setString(0, executeId).list();
    }

    public Page findOwnerHistory(String ygId, int pageNo, int pageSize) {
        Page page = new Page();
        String countSql = "select count(1) from JBPM4_HIST_PROCINST b where b.ID_ in (select distinct PROCINSTID_ from JBPM4_HIST_VAR t where VARNAME_ = 'owner' and VALUE_ = ?)";
        SQLQuery sqlQuery = this.getSession().createSQLQuery(countSql);
        sqlQuery.setString(0, ygId);
        BigDecimal count = (BigDecimal) sqlQuery.uniqueResult();
        String sql = "select b.ID_, NVL((select a.Activity_Name_ from JBPM4_TASK a where a.EXECUTION_ID_=b.ID_),'已审批') ACTIVITY_NAME_," +
                " (select  VALUE_ from (select VALUE_,PROCINSTID_  from JBPM4_HIST_VAR  where  VARNAME_ = 'opera' order by DBID_ desc ) where PROCINSTID_ = b.ID_ and rownum=1) OPERA_," +
                " (select  VALUE_ from (select VALUE_,PROCINSTID_  from JBPM4_HIST_VAR  where  VARNAME_ = 'time' order by DBID_ desc ) where PROCINSTID_ = b.ID_ and rownum=1) TIME_ " +
                " from JBPM4_HIST_PROCINST b where b.ID_ in (select distinct PROCINSTID_ from JBPM4_HIST_VAR t where VARNAME_ = 'owner' and VALUE_ = ?) order by TIME_ desc";
        sqlQuery = this.getSession().createSQLQuery(sql);
        sqlQuery.setString(0, ygId);
        sqlQuery.setFirstResult((pageNo - 1) * pageSize);
        sqlQuery.setMaxResults(pageSize);
        page.setTotalCount(count.intValue());
        page.setResult(sqlQuery.list());
        return page;
    }

    public List findHistory(String executeId) {
        String sql = "select VARNAME_,VALUE_ from JBPM4_HIST_VAR t where PROCINSTID_=? and VARNAME_!='owner' order by DBID_ desc";
        return this.getSession().createSQLQuery(sql).setString(0, executeId).list();
    }


    public List<Yg> findMsgUser(String executeId) {
        String hql = "select yg from Yg yg left join yg.ssgwList gwList where gwList.id=-1 and yg.zgjg.id in (select ppi.userId from org.jbpm.pvm.internal.task.TaskImpl task left  join  task.participations  ppi where task.executionId like ?)";
        List<Yg> ygList = this.find(hql, executeId + "%");
        return ygList;
    }

    public List<FlowTask> findAllTaskYg(String flowName, String taskName) {
        String hql = "From FlowTask where flowName=? and taskName=? ";
        return this.find(hql, flowName, taskName);
    }

    public List<FlowTask> findAllTaskYg(String flowName, String taskName, Long gsId) {
        String hql = "From FlowTask where flowName=? and taskName=?  and gsId=?";
        return this.find(hql, flowName, taskName, gsId);
    }

    public Boolean checkFlowStart(String flowName) {
        String hql = "From FlowConfigure where flowName=?";
        Object object = this.findUnique(hql, flowName);
        if (object != null) {
            return ((FlowConfigure) object).getFlowStart();
        } else
            return false;
    }

    /**
     * 大屏展示：流程审批
     */
    public Map<String,Object> findLcspForScreen(){
        String sql = "select gsjg.id, substr(gsjg.jc,1,2)||'---'||gsjg.qc gs, count(p1.dbid_) sl " +
                "  from (select distinct gsjg.id,gsjg.qc,gs.jc from   " +
                "  T_SYS_GSJG gs,T_SYS_YG yg,T_SYS_YG_SSGW yggw,T_SYS_GSJG gsjg " +
                "  where gsjg.sjjg_id=gs.id and  gsjg.type = 'nbbm'  " +
                " and yg.zgjg_id=gsjg.id and yggw.yg_id=yg.id and yggw.gw_id=-1) gsjg  " +
                "  left outer join ( " +
                "  select taskimpl0_.dbid_,participat1_.groupid_,participat1_.userid_ " +
                "  from JBPM4_TASK taskimpl0_ left outer join JBPM4_PARTICIPATION participat1_ " +
                "  on taskimpl0_.DBID_ = participat1_.TASK_ where participat1_.TASK_ = taskimpl0_.DBID_ " +
                "  and participat1_.TYPE_ = 'candidate' and (taskimpl0_.ASSIGNEE_ is null)) p1 " +
                "    on p1.USERID_ = gsjg.id or p1.GROUPID_ in ( select distinct groupimpl7_.ID_" +
                "     from JBPM4_ID_MEMBERSHIP membership5_ " +
                "  inner join JBPM4_ID_USER userimpl6_ on membership5_.USER_ = userimpl6_.DBID_ " +
                "  inner join JBPM4_ID_GROUP groupimpl7_ on membership5_.GROUP_ = groupimpl7_.DBID_ " +
                "  where userimpl6_.ID_ = gsjg.id) " +
                " group by gsjg.id, gsjg.qc,gsjg.jc having count(p1.dbid_)>0  order by sl desc";
        Query sqlQuery = getSession().createSQLQuery(sql);
        sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        Map<String,Object> result = new HashMap<String, Object>();
        result.put("rows",sqlQuery.list());
        return result;
    }
}
